using System;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using System.Configuration;
using System.Runtime.InteropServices;

//using PCS's namespaces
using PCSComUtils.DataAccess;
using PCSComUtils.PCSExc;
using PCSComUtils.Common;

namespace PCSComProduction.ScrapRecording.DS
{
	public class PRO_AOScrapMasterDS 
	{
		#region Declaration, Constructor
		private const string THIS = "PCSComProduction.ScrapRecording.DS.PRO_AOScrapMasterDS";

		public PRO_AOScrapMasterDS()
		{
		}		
		
		#endregion Declaration, Constructor
	
		#region Update Data Methods
		
		///    <summary>
		///       This method uses to add data to PRO_AOScrapMaster
		///    </summary>
		///    <Inputs>
		///        PRO_AOScrapMasterVO       
		///    </Inputs>
		///    <Returns>
		///       void
		///    </Returns>
		///    <History>
		///       Tuesday, June 07, 2005
		///    </History>		
		public int AddAndReturnID(object pobjObjectVO)
		{
			const string METHOD_NAME = THIS + ".Add()";
			 
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				PRO_AOScrapMasterVO objObject = (PRO_AOScrapMasterVO) pobjObjectVO;
				
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);				
				
				string strSql = "INSERT INTO PRO_AOScrapMaster("
					+ PRO_AOScrapMasterTable.SCRAPNO_FLD + ","
					+ PRO_AOScrapMasterTable.POSTDATE_FLD + ","
					+ PRO_AOScrapMasterTable.CCNID_FLD + ","
					+ PRO_AOScrapMasterTable.MASTERLOCATIONID_FLD + ")"
					+ "VALUES(?,?,?,?)";

				strSql += "; Select @@IDENTITY as NEWID";

				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_AOScrapMasterTable.SCRAPNO_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[PRO_AOScrapMasterTable.SCRAPNO_FLD].Value = objObject.ScrapNo;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_AOScrapMasterTable.POSTDATE_FLD, OleDbType.Date));
				ocmdPCS.Parameters[PRO_AOScrapMasterTable.POSTDATE_FLD].Value = objObject.PostDate;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_AOScrapMasterTable.CCNID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_AOScrapMasterTable.CCNID_FLD].Value = objObject.CCNID;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_AOScrapMasterTable.MASTERLOCATIONID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_AOScrapMasterTable.MASTERLOCATIONID_FLD].Value = objObject.MasterLocationID;

				ocmdPCS.Connection.Open();
				return int.Parse(ocmdPCS.ExecuteScalar().ToString());
			}
			catch(OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
				{																   
					throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);				
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
				}
			}
			catch(InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}
			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}

		
		///    <summary>
		///       This method uses to add data to PRO_AOScrapMaster
		///    </summary>
		///    <Inputs>
		///        PRO_AOScrapMasterVO       
		///    </Inputs>
		///    <Returns>
		///       void
		///    </Returns>
		///    <History>
		///       Tuesday, June 07, 2005
		///    </History>


		public void Add(object pobjObjectVO)
		{
			const string METHOD_NAME = THIS + ".Add()";
			 
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				PRO_AOScrapMasterVO objObject = (PRO_AOScrapMasterVO) pobjObjectVO;
				
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);				
				
				string strSql = "INSERT INTO PRO_AOScrapMaster("
					+ PRO_AOScrapMasterTable.SCRAPNO_FLD + ","
					+ PRO_AOScrapMasterTable.POSTDATE_FLD + ","
					+ PRO_AOScrapMasterTable.CCNID_FLD + ","
					+ PRO_AOScrapMasterTable.MASTERLOCATIONID_FLD + ")"
					+ "VALUES(?,?,?,?)";
				
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_AOScrapMasterTable.SCRAPNO_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[PRO_AOScrapMasterTable.SCRAPNO_FLD].Value = objObject.ScrapNo;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_AOScrapMasterTable.POSTDATE_FLD, OleDbType.Date));
				ocmdPCS.Parameters[PRO_AOScrapMasterTable.POSTDATE_FLD].Value = objObject.PostDate;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_AOScrapMasterTable.CCNID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_AOScrapMasterTable.CCNID_FLD].Value = objObject.CCNID;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_AOScrapMasterTable.MASTERLOCATIONID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_AOScrapMasterTable.MASTERLOCATIONID_FLD].Value = objObject.MasterLocationID;

				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();
			}
			catch(OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
				{
																   
					throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);				
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
				}
			}			

			catch(InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}
	
		
		///    <summary>
		///       This method uses to add data to PRO_AOScrapMaster
		///    </summary>
		///    <Inputs>
		///        PRO_AOScrapMasterVO       
		///    </Inputs>
		///    <Returns>
		///       void
		///    </Returns>
		///    <History>
		///       Tuesday, June 07, 2005
		///    </History>
		
	
		public void Update(object pobjObjecVO)
		{
			const string METHOD_NAME = THIS + ".Update()";

			PRO_AOScrapMasterVO objObject = (PRO_AOScrapMasterVO) pobjObjecVO;
			

			//prepare value for parameters
			OleDbConnection oconPCS =null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = "UPDATE PRO_AOScrapMaster SET "
					+ PRO_AOScrapMasterTable.SCRAPNO_FLD + "= ?, "
					+ PRO_AOScrapMasterTable.POSTDATE_FLD + "= ?,"
					+ PRO_AOScrapMasterTable.CCNID_FLD + "= ?, "
					+ PRO_AOScrapMasterTable.MASTERLOCATIONID_FLD + "=?"
					+ " WHERE " + PRO_AOScrapMasterTable.AOSCRAPMASTERID_FLD + "= ?";

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				
				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_AOScrapMasterTable.SCRAPNO_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[PRO_AOScrapMasterTable.SCRAPNO_FLD].Value = objObject.ScrapNo;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_AOScrapMasterTable.POSTDATE_FLD, OleDbType.Date));
				ocmdPCS.Parameters[PRO_AOScrapMasterTable.POSTDATE_FLD].Value = objObject.PostDate;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_AOScrapMasterTable.CCNID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_AOScrapMasterTable.CCNID_FLD].Value = objObject.CCNID;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_AOScrapMasterTable.MASTERLOCATIONID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_AOScrapMasterTable.MASTERLOCATIONID_FLD].Value = objObject.MasterLocationID;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_AOScrapMasterTable.AOSCRAPMASTERID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_AOScrapMasterTable.AOSCRAPMASTERID_FLD].Value = objObject.AOScrapMasterID;
				
				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();	
			}
			catch(OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
				{
																   
					throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);				
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
				}
			}			

			catch(InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}
			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}	

		
		///    <summary>
		///       This method uses to delete data from PRO_AOScrapMaster table
		///    </summary>
		///    <Inputs>
		///        PRO_AOScrapMaster Id     
		///    </Inputs>
		///    <Returns>
		///       void
		///    </Returns>
		///    <History>
		///       Tuesday, June 07, 2005
		///    </History>

		public void Delete(int pintID)
		{
			const string METHOD_NAME = THIS + ".Delete()";
			string strSql = "DELETE " + PRO_AOScrapMasterTable.TABLE_NAME + " WHERE  " + "AOScrapMasterID" + "=" + pintID.ToString();
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();	
				ocmdPCS = null;
			}
			catch(OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLCASCADE_PREVENT_KEYCODE)
				{																   
					throw new PCSDBException(ErrorCode.CASCADE_DELETE_PREVENT, METHOD_NAME, ex);				
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
				}
			}
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}
			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}
				
		///    <summary>
		///       This method uses to add data to PRO_AOScrapMaster
		///    </summary>
		///    <Inputs>
		///        PRO_AOScrapMasterVO       
		///    </Inputs>
		///    <Returns>
		///       void
		///    </Returns>
		///    <History>
		///       Tuesday, June 07, 2005
		///    </History>
		
		public void UpdateDataSet(DataSet pData)
		{
			const string METHOD_NAME = THIS + ".UpdateDataSet()";
			
			OleDbConnection oconPCS =null;
			OleDbCommandBuilder odcbPCS ;
			OleDbDataAdapter odadPCS = new OleDbDataAdapter();

			try
			{
				string strSql = "SELECT "
					+ PRO_AOScrapMasterTable.AOSCRAPMASTERID_FLD + ","
					+ PRO_AOScrapMasterTable.SCRAPNO_FLD + ","
					+ PRO_AOScrapMasterTable.POSTDATE_FLD + ","
					+ PRO_AOScrapMasterTable.CCNID_FLD + ","
					+ PRO_AOScrapMasterTable.MASTERLOCATIONID_FLD 
					+ "  FROM " + PRO_AOScrapMasterTable.TABLE_NAME;

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				odadPCS.SelectCommand = new OleDbCommand(strSql, oconPCS);
				odcbPCS = new OleDbCommandBuilder(odadPCS);
				pData.EnforceConstraints = false;
				odadPCS.Update(pData, PRO_AOScrapMasterTable.TABLE_NAME);
			}
			catch(OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
				{																   
					throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);				
				}
				else if (ex.Errors[1].NativeError == ErrorCode.SQLCASCADE_PREVENT_KEYCODE)
				{
					throw new PCSDBException(ErrorCode.CASCADE_DELETE_PREVENT, METHOD_NAME, ex);				
				}				
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
				}
			}
			catch(InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}
			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}

		#endregion Update Data Methods
		
		#region Retreive Data Methods
		///    <summary>
		///       This method uses to get data from PRO_AOScrapMaster table into VO object
		///    </summary>
		///    <Inputs>
		///        Master Id       
		///    </Inputs>
		///    <Returns>
		///      PRO_AOScrapMasterVO Object
		///    </Returns>
		///    <History>
		///       Tuesday, June 07, 2005
		///    </History>

		public object GetObjectVO(int pintID)
		{
			const string METHOD_NAME = THIS + ".GetObjectVO()";
			DataSet dstPCS = new DataSet();
			
			OleDbDataReader odrPCS = null;
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql =	"SELECT "
					+ PRO_AOScrapMasterTable.AOSCRAPMASTERID_FLD + ", "
					+ PRO_AOScrapMasterTable.SCRAPNO_FLD + ", "
					+ PRO_AOScrapMasterTable.POSTDATE_FLD + ", "
					+ PRO_AOScrapMasterTable.CCNID_FLD + ", "
					+ PRO_AOScrapMasterTable.MASTERLOCATIONID_FLD
					+ " FROM " + PRO_AOScrapMasterTable.TABLE_NAME
					+" WHERE " + PRO_AOScrapMasterTable.AOSCRAPMASTERID_FLD + "=" + pintID;

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				odrPCS = ocmdPCS.ExecuteReader();

				PRO_AOScrapMasterVO objObject = new PRO_AOScrapMasterVO();

				while (odrPCS.Read())
				{ 
					objObject.AOScrapMasterID = int.Parse(odrPCS[PRO_AOScrapMasterTable.AOSCRAPMASTERID_FLD].ToString().Trim());
					objObject.ScrapNo = odrPCS[PRO_AOScrapMasterTable.SCRAPNO_FLD].ToString().Trim();
					objObject.PostDate = DateTime.Parse(odrPCS[PRO_AOScrapMasterTable.POSTDATE_FLD].ToString().Trim());
					objObject.CCNID = int.Parse(odrPCS[PRO_AOScrapMasterTable.CCNID_FLD].ToString().Trim());
					objObject.MasterLocationID = int.Parse(odrPCS[PRO_AOScrapMasterTable.MASTERLOCATIONID_FLD].ToString().Trim());
				}		
				return objObject;					
			}
			catch(OleDbException ex)
			{			
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			

			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}
			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}		

		///    <summary>
		///       This method uses to add data to PRO_AOScrapMaster
		///    </summary>
		///    <Inputs>
		///        PRO_AOScrapMasterVO       
		///    </Inputs>
		///    <Returns>
		///       void
		///    </Returns>
		///    <History>
		///       Tuesday, June 07, 2005
		///    </History>

		public DataSet List()
		{
			const string METHOD_NAME = THIS + ".List()";
			DataSet dstPCS = new DataSet();
			
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = "SELECT "
					+ PRO_AOScrapMasterTable.AOSCRAPMASTERID_FLD + ","
					+ PRO_AOScrapMasterTable.SCRAPNO_FLD + ","
					+ PRO_AOScrapMasterTable.POSTDATE_FLD + ","
					+ PRO_AOScrapMasterTable.CCNID_FLD + ","
					+ PRO_AOScrapMasterTable.MASTERLOCATIONID_FLD
					+ " FROM " + PRO_AOScrapMasterTable.TABLE_NAME;

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();
				
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstPCS,PRO_AOScrapMasterTable.TABLE_NAME);

				return dstPCS;
			}
			catch(OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}
			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}

		/// <summary>
		/// Get detail information of a specific AOScrap detail
		/// </summary>
		/// <param name="pintAOScrapMasterId"></param>
		/// <returns></returns>
		public DataRow GetAOScrapMaster(int pintAOScrapMasterId)
		{
			const string METHOD_NAME = THIS + ".List()";
			DataSet dstPCS = new DataSet();
			
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = "SELECT "
					+ PRO_AOScrapMasterTable.TABLE_NAME + "." + PRO_AOScrapMasterTable.AOSCRAPMASTERID_FLD + ", " 
					+ PRO_AOScrapMasterTable.TABLE_NAME + "." + PRO_AOScrapMasterTable.SCRAPNO_FLD + ", " 
					+ PRO_AOScrapMasterTable.TABLE_NAME + "." + PRO_AOScrapMasterTable.POSTDATE_FLD + ", " 
					+ PRO_AOScrapMasterTable.TABLE_NAME + "." + PRO_AOScrapMasterTable.CCNID_FLD + ", " 
					+ PRO_AOScrapMasterTable.TABLE_NAME + "." + PRO_AOScrapMasterTable.MASTERLOCATIONID_FLD + ", " 
					+ MST_MasterLocationTable.TABLE_NAME + "." + MST_MasterLocationTable.CODE_FLD + " as " + MST_MasterLocationTable.TABLE_NAME + MST_MasterLocationTable.CODE_FLD + ", " 
					+ MST_CCNTable.TABLE_NAME + "." + MST_CCNTable.CODE_FLD + " AS " + MST_CCNTable.TABLE_NAME + MST_CCNTable.CODE_FLD

					+ " FROM " + MST_MasterLocationTable.TABLE_NAME + ", "
							   + PRO_AOScrapMasterTable.TABLE_NAME + ", "
							   + MST_CCNTable.TABLE_NAME
					+ " WHERE " + MST_MasterLocationTable.TABLE_NAME + "." + MST_MasterLocationTable.MASTERLOCATIONID_FLD + "=" + PRO_AOScrapMasterTable.TABLE_NAME + "." + PRO_AOScrapMasterTable.MASTERLOCATIONID_FLD
					+ " AND " + PRO_AOScrapMasterTable.TABLE_NAME + "." + PRO_AOScrapMasterTable.CCNID_FLD + "=" + MST_CCNTable.TABLE_NAME + "." + MST_CCNTable.CCNID_FLD
					+ " AND " + PRO_AOScrapMasterTable.TABLE_NAME + "." + PRO_AOScrapMasterTable.AOSCRAPMASTERID_FLD + "=" + pintAOScrapMasterId;

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();
				
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstPCS, PRO_AOScrapMasterTable.TABLE_NAME);
				if(dstPCS.Tables[0].Rows.Count != 0)
				{
					return dstPCS.Tables[0].Rows[0];
				}
				else
				{
					return null;
				}
			}
			catch(OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}
			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}
			
		/// <summary>
		/// SELECT OrderQuantity - (SELECT ISNULL(SUM(CompletedQuantity),0) 
		/// FROM PRO_WorkOrderCompletion WHERE WorkOrderDetailID = pintWODetailID GROUP BY WorkOrderDetailID) 
		/// - (SELECT ISNULL(SUM(ScrapQuantity),0) FROM PRO_AOScrapDetail WHERE WorkOrderDetailID = pintWODetailID
		/// GROUP BY WorkOrderDetailID) AS AvailableQuantity FROM PRO_WorkOrderDetail WHERE WorkOrderDetailID = pintWODetailID
		/// 
		/// 
		/// </summary>
		public decimal GetAvailableQuantityByWODetail(int pintWODetailID)
		{
			const string METHOD_NAME = THIS + ".GetAvailableQuantityByWODetail()";
			 
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSQL = "SELECT " + PRO_WorkOrderDetailTable.ORDERQUANTITY_FLD 
					+ " -(SELECT ISNULL(SUM(" + PRO_WorkOrderCompletionTable.COMPLETEDQUANTITY_FLD + "),0) "
					+ " FROM " + PRO_WorkOrderCompletionTable.TABLE_NAME 
					+ " WHERE " + PRO_WorkOrderCompletionTable.WORKORDERDETAILID_FLD + "=" + pintWODetailID 
					+ " GROUP BY " + PRO_WorkOrderCompletionTable.WORKORDERDETAILID_FLD + ") "
					+ " -(SELECT ISNULL(SUM(" + PRO_AOScrapDetailTable.SCRAPQUANTITY_FLD + "),0)"
					+ " FROM " + PRO_AOScrapDetailTable.TABLE_NAME 
					+ " WHERE " + PRO_AOScrapDetailTable.WORKORDERDETAILID_FLD + "=" + pintWODetailID
					+ " GROUP BY " + PRO_AOScrapDetailTable.WORKORDERDETAILID_FLD + ") AS AvailableQuantity "
					+ " FROM " + PRO_WorkOrderDetailTable.TABLE_NAME
					+ " WHERE " + PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD + "=" + pintWODetailID;
			
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSQL, oconPCS);
				
				ocmdPCS.Connection.Open();

				return decimal.Parse(ocmdPCS.ExecuteScalar().ToString());
			}
			catch(OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
				{
																   
					throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);				
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
				}
			}
			catch(InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}
			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}

		#endregion Retreive Data Methods
	}
}